package com.hejjon.service.impl;

import com.hejjon.core.bean.TableColumn;
import com.hejjon.core.bean.TableField;
import com.hejjon.core.enumer.JdbcTypeEnum;
import com.hejjon.domain.pojo.FilterCondition;
import com.hejjon.service.ISqlBuilderService;
import com.hejjon.util.DBUtil;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import static com.hejjon.core.constant.TableFieldConstants.*;

/**
 * @author: cs
 * @date: 2025-04-09 17:27:20
 * @since: 1.0
 */
@Service
public class SqlBuilderServiceImpl implements ISqlBuilderService {


    @Override
    public String getCreateTableSql(String tableName, String tableComment, List<TableColumn> columnList) {

        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE `");
        sb.append(tableName).append("` (").append("\n");
        List<String> temps = new ArrayList<>();
        for (TableColumn column : columnList) {
            String jdbcType = column.getJdbcType();
            StringBuilder tmp = new StringBuilder();
            tmp.append("  `").append(column.getColumnName()).append("` ");
            tmp.append(jdbcType.toLowerCase(Locale.ROOT)).append(" ");
            if (!DBUtil.noNeedLength(jdbcType) && column.getLength() > 0) {
                tmp.append("(").append(column.getLength()).append(") ");
            }
            if (column.getNotNull() || column.getPrimaryKey()) {
                tmp.append("NOT NULL ");
            }
            if (!notNeedSetDefaultValue(column)) {
                if (column.getDefaultValue() == null) {
                    tmp.append("DEFAULT NULL ");
                } else {
                    String defaultValueStr = calcDefaultValue(column);
                    tmp.append("DEFAULT ").append(defaultValueStr).append(" ");
                }
            }
            if (StringUtils.isNotEmpty(column.getComment())) {
                tmp.append("COMMENT ").append("'").append(column.getComment()).append("'");
            }
            temps.add(tmp.toString());
        }
        String join = StringUtils.join(temps, ",\n");
        sb.append(join).append(",\n");
        // 设置主键
        sb.append("  PRIMARY KEY (`id`) USING BTREE\n");
        sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        sb.append(" COMMENT='").append(tableComment).append("';");

        return sb.toString();
    }

    private String calcDefaultValue(TableColumn column) {
        String jdbcType = column.getJdbcType();
        String defaultValueStr = "";
        if (column.getDefaultValue() == null || "".equals(column.getDefaultValue().toString())) {
            return "NULL";
        }
        JdbcTypeEnum jdbcTypeEnum = JdbcTypeEnum.getInstance(jdbcType);
        switch (jdbcTypeEnum) {
            case VARCHAR:
            case INT:
            case DOUBLE:
                defaultValueStr = "'" + column.getDefaultValue().toString() + "'";
                break;
            case DATE:
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                defaultValueStr = df.format((Date) column.getDefaultValue());
                break;
            case DATETIME:
                DateFormat df2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                defaultValueStr = "'" + df2.format((Date) column.getDefaultValue()) + "'";
                break;
        }

        return defaultValueStr;
    }


    private boolean notNeedSetDefaultValue(TableColumn column) {
        String[] columnNames = new String[] {FIELD_ID, FIELD_PARENT_ID, FIELD_FEATURE_ID};
        if (ArrayUtils.contains(columnNames, column.getColumnName())) {
            return true;
        }
        String jdbcType = column.getJdbcType();
        String[] types = new String[] {"text", "longtext", "blob", "json"};
        return ArrayUtils.contains(types, jdbcType);
    }



    @Override
    public String createDB(String databaseName) {
        return "CREATE DATABASE `" + databaseName + "` CHARACTER SET 'utf8mb4'";
    }

    @Override
    public String createDB(String databaseName, boolean deleteExist) {
        String sql = "";
        if (deleteExist) {
            sql += "DROP DATABASE IF EXISTS `" + databaseName + "`; ";
        }
        sql += "CREATE DATABASE `" + databaseName + "` CHARACTER SET 'utf8mb4';";
        return sql;
    }

    @Override
    public String dropIfDatabaseExists(String databaseName) {

        return "DROP DATABASE IF EXISTS `" + databaseName + "`;";
    }

    @Override
    public List<String> batchDropDatabaseExists(List<String> dbNames) {
        List<String> sqlList = new ArrayList<>();
        dbNames.forEach(dname -> {
            String tmp = "DROP DATABASE IF EXISTS `" + dname + "`;";
            sqlList.add(tmp);
        });
        return sqlList;
    }

    @Override
    public String dropIfTableExists(String tableName) {
        return "DROP TABLE IF EXISTS `" + tableName + "`;";
    }

    @Override
    public List<String> dropIfTableExists(String databaseName, List<String> tableNameList) {
        return tableNameList.stream().map(tableName -> "DROP TABLE IF EXISTS `" + databaseName + "`.`" + tableName + "`;").collect(Collectors.toList());
    }

    @Override
    public String useDatabase(String databaseName) {
        return "USE " + databaseName;
    }




    @Override
    public String buildInsert(String tableName, List<TableField> fieldList, List<Map<String, Object>> valueList) {
        String sql = "INSERT INTO `" + tableName + "` (";
        List<String> dotFieldList = fieldList.stream().map(field -> "`" + field.getName() + "`").collect(Collectors.toList());
        String join = StringUtils.join(dotFieldList, ", ");
        sql = sql + join + ") " + "VALUES ";
        List<String> valueRowStrList = new ArrayList<>();
        for (int i = 0; i < valueList.size(); i++) {
            List<String> valueRow = new ArrayList<>();      // 一行数据
            for (int j = 0; j < fieldList.size(); j++) {
                valueRow.add("?");
            }
            String valueRowStr = "(" + StringUtils.join(valueRow, ", ") + ")";
            valueRowStrList.add(valueRowStr);
        }
        String valuesStr = StringUtils.join(valueRowStrList, ", ");
        sql += valuesStr + ";";
        return sql;
    }

    @Override
    public String buildQueryScript(String tableName, List<TableField> fieldList, List<FilterCondition> conditionList, List<TableField> sortFieldList) {
        StringBuilder sql = new StringBuilder("SELECT ");
        List<String> dotFieldList = fieldList.stream().map(field -> "`" + field.getName() + "`").collect(Collectors.toList());
        String queryColumns = StringUtils.join(dotFieldList, ", ");
        sql.append(queryColumns).append(" from ").append("`").append(tableName).append("` ");
        sql.append(" where 1=1 ");
        for (FilterCondition condition : conditionList) {
            sql.append(condition.getJoin()).append(" ");
            sql.append("`").append(condition.getFiledName()).append("` ");
            sql.append(condition.getOper()).append(" ? ");
        }
        // 处理排序
        sql.append("ORDER BY ");
        for (TableField sortField : sortFieldList) {
            sql.append("`").append(sortField.getName()).append("` ").append(sortField.getDirection());
        }
        sql.append(";");
        return sql.toString();
    }


    @Override
    public String buildUpdate(String tableName, List<TableField> updateFieldList) {
        StringBuilder sql = new StringBuilder("UPDATE `" + tableName + "` SET ");
        List<String> updFields = new ArrayList<>();
        for (TableField field : updateFieldList) {
            updFields.add("`" + field.getName() + "` = " + " ? ");
        }
        String updFieldStr = StringUtils.join(updFields, ", ");
        sql.append(updFieldStr).append(" WHERE `id` = ?");

        return sql.toString();
    }
}
